package cqut.wys.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.io.*;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

@RestController
public class ReadCSVController {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    private List<String[]> errorList = new ArrayList<>();
    private List<String> errorSqlList = new ArrayList<>();

    @GetMapping("/readCsv")
    public String readCsv() {
        new Thread(() -> {
            LocalDateTime startDate = LocalDateTime.now();
            readCsv("");
            LocalDateTime endDate = LocalDateTime.now();
            System.out.println("开始时间:" + startDate);
            System.out.println("结束时间:" + endDate);
        }).start();
        return "ok";
    }

    private void readCsv(String filePath) {
        int i = 0;
        try {
            InputStreamReader inputStreamReader = new FileReader(filePath);
            BufferedReader bufferedReader = new BufferedReader(inputStreamReader);
            String line;
            List<String[]> list = new ArrayList<>(51);
            while ((line = bufferedReader.readLine()) != null) {
                System.out.println(i);
                //CSV格式文件为逗号分隔符文件，这里根据逗号切分
                String[] item = line.split(",");
                if (i != 0) {
                    list.add(item);
                }
                if (i > 0 && (i % 200) == 0) {
                    saveData(list, i);
                    list.clear();
                }
                i++;
            }
            System.out.println("错误行");
            for (String[] strings : errorList) {
                System.out.println("error:"+Arrays.toString(strings));
            }
            System.out.println("错误sql");
            for (String s : errorSqlList) {
                System.out.println(s);
            }
            System.out.println("错误行ooo="+errorList.size());
            System.out.println("错误sqlooo="+errorSqlList.size());
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    private void saveData(List<String[]> list, int row) {
        StringBuilder sb = new StringBuilder("INSERT INTO `rujia`.`rj_user_info_10` (`old_id`,`version`, `family`, `c_zip`, `c_address`, `c_tel`, " +
                "`company`, `education`, `taste`, `nation`, `email`, `fax`, `tel`, `mobile`, `duty`, `last_nm`, `first_nm`, `district1`, `district2`, " +
                "`district3`, `district4`, `district5`, `district6`, `dirty`, `zip`, `address`, `birthday`," +
                "`gender`, `ctf_id`, `ctf_tp`, `descriot`, `card_no`, `name`) VALUES ");

        for (String[] strings : list) {
            int i = strings.length - 1;
            if (i != 32) {
                errorList.add(strings);
//                System.out.println("错误行:" + Arrays.toString(strings));
                continue;
            }
            sb.append("(");
            for (; i >= 0; i--) {
                sb.append("'").append(strings[i]).append("'");
                if (i != 0) {
                    sb.append(",");
                }
            }
            sb.append("),");
        }
        sb.deleteCharAt(sb.length() - 1);
        String sql = sb.toString();
        try {
            jdbcTemplate.execute(sql);
        } catch (Exception e) {
            for (String[] strings : list) {
                saveDataByOneRow(strings);
            }
        }
    }

    private void saveDataByOneRow(String[] strings) {
        StringBuilder sb = new StringBuilder("INSERT INTO `rujia`.`rj_user_info_10` (`old_id`,`version`, `family`, `c_zip`, `c_address`, `c_tel`, " +
                "`company`, `education`, `taste`, `nation`, `email`, `fax`, `tel`, `mobile`, `duty`, `last_nm`, `first_nm`, `district1`, `district2`, " +
                "`district3`, `district4`, `district5`, `district6`, `dirty`, `zip`, `address`, `birthday`," +
                "`gender`, `ctf_id`, `ctf_tp`, `descriot`, `card_no`, `name`) VALUES ");
        int i = strings.length - 1;
        if (i != 32) {
            errorList.add(strings);
//            System.out.println("错误行:" + Arrays.toString(strings));
            return;
        }
        sb.append("(");
        for (; i >= 0; i--) {
            sb.append("'").append(strings[i]).append("'");
            if (i != 0) {
                sb.append(",");
            }
        }
        sb.append(")");
        String sql = sb.toString();
        try {
            jdbcTemplate.execute(sql);
        } catch (Exception e) {
//            System.out.println("执行错误的Sql:" + sql);
            errorSqlList.add(sql);
        }
    }
}
